﻿using Comm;
using Kinlo.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kinlo.Repo
{
    public class CodeFirstRep
    {
        string strConn;
        public CodeFirstRep()
        {
            strConn = System.Configuration.ConfigurationManager.ConnectionStrings["WeightDB"].ConnectionString;

        }
        public int DeleteCodeFirst(CodeFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {

                    var cmd = string.Format(@"DELETE dbo.Code_First WHERE Code='{0}'", w.Code);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "DeleteCodeFirst", ex.Message));
                return 0;
            }
        }

        public int Add2(CodeFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    var cmd = string.Format(@"INSERT dbo.{0}  ( ID,CodeIndex, Code, ProductDate ,IsValidate,OnceBeforWeight,OnceAfterWeight,TwoBeforWeight,TwoAfterWeight,ShortVoltage,ShortDuration,Snowflake)
                        VALUES  (  @ID,@CodeIndex, @Code, @ProductDate ,@IsValidate,@OnceBeforWeight,@OnceAfterWeight,@TwoBeforWeight,@TwoAfterWeight,@ShortVoltage,@ShortDuration,@Snowflake)", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new SqlParameter("@Code", w.Code == null ? DBNull.Value : (object)w.Code));
                    sqlCmd.Parameters.Add(new SqlParameter("@CodeIndex", w.CodeIndex == null ? DBNull.Value : (object)w.CodeIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@IsValidate", w.IsValidate == null ? DBNull.Value : (object)w.IsValidate));
                    sqlCmd.Parameters.Add(new SqlParameter("@OnceBeforWeight", w.OnceBeforWeight == null ? DBNull.Value : (object)w.OnceBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@OnceAfterWeight", w.OnceAfterWeight == null ? DBNull.Value : (object)w.OnceAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoBeforWeight", w.TwoBeforWeight == null ? DBNull.Value : (object)w.TwoBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoAfterWeight", w.TwoAfterWeight == null ? DBNull.Value : (object)w.TwoAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@ShortVoltage", w.ShortVoltage == null ? DBNull.Value : (object)w.ShortVoltage));
                    sqlCmd.Parameters.Add(new SqlParameter("@ShortDuration", w.ShortDuration == null ? DBNull.Value : (object)w.ShortDuration));
                    sqlCmd.Parameters.Add(new SqlParameter("@Snowflake", w.Snowflake));
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "Add2", ex.Message));
                return 0;
            }
        }


        public int AddReWork(ReWork w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"INSERT ReWork(ID,Barcode,OneBeforWeight,OneAfterWeight,TwoBeforeWeight,TwoAfterWeight,BeforeWeight,AftweWeight,InjValue,HoldInjValue,InjType,ProductDate,Result,RWorkOper,LossVal)
                        VALUES  (@ID,@Barcode,@OneBeforWeight,@OneAfterWeight,@TwoBeforeWeight,@TwoAfterWeight,@BeforeWeight,@AftweWeight,@InjValue,@HoldInjValue,@InjType,@ProductDate,@Result,@RWorkOper,@LossVal)");
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new SqlParameter("@Barcode", w.Barcode == null ? DBNull.Value : (object)w.Barcode));
                    sqlCmd.Parameters.Add(new SqlParameter("@RWorkOper", w.RWorkOper == null ? DBNull.Value : (object)w.RWorkOper));
                    sqlCmd.Parameters.Add(new SqlParameter("@OneBeforWeight", w.OneBeforWeight == null ? DBNull.Value : (object)w.OneBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@OneAfterWeight", w.OneAfterWeight == null ? DBNull.Value : (object)w.OneAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoBeforeWeight", w.TwoBeforeWeight == null ? DBNull.Value : (object)w.TwoBeforeWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoAfterWeight", w.TwoAfterWeight == null ? DBNull.Value : (object)w.TwoAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforeWeight", w.BeforeWeight == null ? DBNull.Value : (object)w.BeforeWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@AftweWeight", w.AftweWeight == null ? DBNull.Value : (object)w.AftweWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValue", w.InjValue == null ? DBNull.Value : (object)w.InjValue));
                    sqlCmd.Parameters.Add(new SqlParameter("@HoldInjValue", w.HoldInjValue == null ? DBNull.Value : (object)w.HoldInjValue));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjType", w.InjType == null ? DBNull.Value : (object)w.InjType));
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Result", w.Result == null ? DBNull.Value : (object)w.Result));
                    sqlCmd.Parameters.Add(new SqlParameter("@LossVal", w.LossVal == null ? DBNull.Value : (object)w.LossVal));


                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "AddReWork", ex.Message));
                return 0;
            }
        }

        public int UpdateReWork(ReWork w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format($"Update ReWork set Barcode=@Barcode,OneBeforWeight=@OneBeforWeight,OneAfterWeight=@OneAfterWeight,TwoBeforeWeight=@TwoBeforeWeight,TwoAfterWeight=@TwoAfterWeight,BeforeWeight=@BeforeWeight,AftweWeight=@AftweWeight,InjValue=@InjValue,HoldInjValue=@HoldInjValue,InjType=@InjType,ProductDate=@ProductDate,Result=@Result,RWorkOper=@RWorkOper,LossVal=@LossVal where ID='{w.ID}'");
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new SqlParameter("@Barcode", w.Barcode == null ? DBNull.Value : (object)w.Barcode));
                    sqlCmd.Parameters.Add(new SqlParameter("@RWorkOper", w.RWorkOper == null ? DBNull.Value : (object)w.RWorkOper));
                    sqlCmd.Parameters.Add(new SqlParameter("@OneBeforWeight", w.OneBeforWeight == null ? DBNull.Value : (object)w.OneBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@OneAfterWeight", w.OneAfterWeight == null ? DBNull.Value : (object)w.OneAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoBeforeWeight", w.TwoBeforeWeight == null ? DBNull.Value : (object)w.TwoBeforeWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@TwoAfterWeight", w.TwoAfterWeight == null ? DBNull.Value : (object)w.TwoAfterWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforeWeight", w.BeforeWeight == null ? DBNull.Value : (object)w.BeforeWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@AftweWeight", w.AftweWeight == null ? DBNull.Value : (object)w.AftweWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValue", w.InjValue == null ? DBNull.Value : (object)w.InjValue));
                    sqlCmd.Parameters.Add(new SqlParameter("@HoldInjValue", w.HoldInjValue == null ? DBNull.Value : (object)w.HoldInjValue));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjType", w.InjType == null ? DBNull.Value : (object)w.InjType));
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Result", w.Result == null ? DBNull.Value : (object)w.Result));
                    sqlCmd.Parameters.Add(new SqlParameter("@LossVal", w.LossVal == null ? DBNull.Value : (object)w.LossVal));

                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "UpdateReWork", ex.Message));
                return 0;
            }
        }


        public ReWork GetReWork(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format($"select top 1 * from rework where barcode='{barcode}' order by ProductDate desc ");
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToModel<ReWork>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "GetReWork", ex.Message));
                return null;
            }
        }
        public List<ReWork> GetReWorks(string time1, string time2, string code, string okng = "")
        {
            try
            {
                if (string.IsNullOrEmpty(code))
                {
                    code = "";
                }
                else
                {
                    code = $" AND Barcode LIKE '%{code}%'";
                }

                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = $"SELECT * FROM  ReWork WHERE ProductDate BETWEEN '{time1}' AND '{time2}' {code}  ORDER BY ProductDate DESC ";
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<ReWork>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "GetCodeFirst", ex.Message));
                return null;
            }
        }


        /// <summary>
        /// 更新状态 是否已失效
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public int Update(string code)
        {
            try
            {
                //var cmd = @"UPDATE Code_First
                //        SET IsValidate=0
                //        WHERE Code=@Code";

                //return DB.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, cmd,
                //         new SqlParameter("@Code", code == null ? DBNull.Value : (object)code));
                return 0;

            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "Update", ex.Message));
                return 0;
            }
        }

        public CodeFirst GetCodeFirst(string code)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    var cmd = String.Format(@"SELECT top 1 * FROM dbo.{1} WHERE Code='{0}' ORDER BY ProductDate desc", code, table);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToModel<CodeFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "Update", ex.Message));
                return null;
            }

        }

        /// <summary>
        /// 根据雪花ID获取
        /// </summary>
        /// <param name="snowflake"></param>
        /// <returns></returns>
        public CodeFirst GetCodeFirstBySnowflake(string snowflake)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    var cmd = String.Format(@"SELECT top 1 * FROM dbo.{1} WHERE Snowflake='{0}' ORDER BY ProductDate desc", snowflake, table);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToModel<CodeFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "Update", ex.Message));
                return null;
            }

        }
        public IList<CodeFirst> GetCodeFirst1()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    // var cmd = @"SELECT * FROM dbo.Code_First WHERE IsValidate=1 ORDER BY ProductDate DESC";
                    var cmd = @"SELECT TOP 1000 * FROM dbo.Code_First WHERE IsValidate=1 AND	ProductDate>DATEADD(DAY,-7,GETDATE()) ORDER BY ProductDate DESC";
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var CodeFirst = sqlCmd.ExecuteReader().ReaderToList<CodeFirst>();
                    if (CodeFirst.Count > 0)
                        return CodeFirst;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "Update", ex.Message));
                return null;
            }
        }


        public List<CodeFirst> GetCodeFirst()
        {
            //var cmd = @"SELECT * FROM dbo.Code_First WHERE IsValidate=1 ORDER BY ProductDate DESC";
            //return DB.SqlQuery<CodeFirst>(cmd).ToList();
            return null;
        }

        public int UpdateCodeFirst(CodeFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    // w1.ShortVoltage = voltage;
                    // w1.ShortDuration = testTimer;
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    var cmd = string.Format(@"UPDATE dbo.{10}
                    SET CodeIndex='{0}',Code='{1}',ProductDate='{2}',IsValidate='{3}',ShortResult='{4}',ShortValue='{5}',ShortUnit='{6}',ShortUnitSeq='{7}',ShortResult2='{8}',ShortVoltage='{11}',ShortDuration='{12}' 
                    WHERE ID='{9}'", w.CodeIndex, w.Code, w.ProductDate, w.IsValidate, w.ShortResult, w.ShortValue, w.ShortUnit, w.ShortUnitSeq, w.ShortResult2, w.ID, table, w.ShortVoltage, w.ShortDuration);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "UpdateCodeFirst", ex.Message));
                return 0;
            }
        }

        /// <summary>
        /// 更新短路测试数据
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        public int UpdateCodeFirstNew(CodeFirst c)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    string table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    string cmdStr = $@"Update dbo.{table} set CodeIndex='{c.CodeIndex}',Code='{c.Code}',ProductDate='{c.ProductDate}',IsValidate='{c.IsValidate}',ShortResult='{c.ShortResult}',
                    ShortUnit='{c.ShortUnit}',ShortUnitSeq='{c.ShortUnitSeq}',ShortResult2='{c.ShortResult2}',ShortVoltage='{c.ShortVoltage}',ShortDuration='{c.ShortDuration}',
                    Drop1='{c.Drop1}',Drop2='{c.Drop2}',Drop1Result='{c.Drop1Result}',Drop2Result='{c.Drop2Result}',Drop3='{c.Drop3}',Drop3Result='{c.Drop3Result}',ResidueVoltage='{c.ResidueVoltage}',ResidueVoltageResult='{c.ResidueVoltageResult}',OpenResult='{c.OpenResult}',Result='{c.Result}',THResult='{c.TH_Result}',
                    TLResult='{c.TL_Result}',UpperTime='{c.UpperTime}',VoltageResult1='{c.VoltageResult1}',VoltageResult2='{c.VoltageResult2}',VPResult='{c.VPResult}',Voltage='{c.Voltage}',Resistance='{c.Resistance}',ResistanceResult='{c.ResistanceResult}',
                    VP='{c.VP}',VD1='{c.VD1}',VD2='{c.VD2}',VD3='{c.VD3}',R='{c.R}',TF='{c.TF}',TK='{c.TK}',RT='{c.RT}' Where ID='{c.ID}'";
                    SqlCommand cmd = new SqlCommand(cmdStr, conn);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"Class:CodeFirstRep method:UpdateCodeFirstNew 发生异常:{ex.Message}");
                return 0;
            }
        }


        public List<CodeFirst> GetCodeFirst(string time1, string time2, string code, string okngStatus)
        {
            try
            {
                if (string.IsNullOrEmpty(code))
                {
                    code = "%";
                }
                else
                {
                    code = "%" + code + "%";
                }

                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "Code_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "Code_Second" : "Code_Thirdly");
                    var cmd = string.Format(@"SELECT *
                        FROM dbo.{0} WHERE ProductDate BETWEEN '{1}' AND '{2}' AND Code LIKE '{3}' ORDER BY ProductDate DESC ",
                       table, time1, time2, code);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<CodeFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "CodeFirstRep", "GetCodeFirst", ex.Message));
                return null;
            }
        }


        /// <summary>
        /// 请除 day 天前的记录
        /// </summary>
        /// <param name="days"></param>
        /// <returns></returns>
        public bool ClearRecordsByDay(int days)
        {
            //DelRecordsByDays
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@" EXEC dbo.ClearRecordByDay @day ='{0}'  ", days);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

    }

}
